package es.ats.codington.festival.utils;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;

import es.ats.codington.festival.domain.Building;
import es.ats.codington.festival.domain.Place;

/**
 * The Class PlaceDBQuery.
 */
public class PlaceDBQuery {

	/** The adress. */
	static String adress = "src/es/ats/codington/festival/utils/DBJson.json";	
	
	/**
	 * The main method.
	 *
	 * @param args the arguments
	 */
	public static void main(String[] args) {
		try{
			//Obtaining a connection
			Connection con = FERSDataConnection.getConnection();
		/**	if(con != null)
			{
			System.out.println("Connection successfully established!");
			}*/
						
			//Creating a PreparedStatement with a query "select * from Employee" 
			PreparedStatement pStmt = con.prepareStatement("select * from visitor");
			
			//Executing this query using executeQuery()
			ResultSet rs = pStmt.executeQuery();
			
			//Process the resultSet and print all records on console
		/**	while(rs.next())
			{
				System.out.println(rs.getString(1)+"		"+ rs.getString(2) + "		" + rs.getString(3));
				
			}*/
			//Closing the connection
			con.close();
			
		}catch (Exception e){
			System.out.println(e.getMessage());
		}

	}
	
	
	//Inserts
	
	/**
	 * Ins place table.
	 *
	 * @param conec the conec
	 * @param place the place
	 * @param chain the chain
	 * @return the int
	 * @throws Exception the exception
	 */
	public static int insPlaceTable(Connection conec, Place place, String chain) throws Exception {
		int rs=0; 
	    try{
	    	PreparedStatement pStmt = null; 
	    	pStmt = conec.prepareStatement(chain);
			
			pStmt.setString(1,place.getPlaceName());
			pStmt.setInt(2,place.getCapacity());
			pStmt.setString(3,place.getPlaceDescription());
			pStmt.setString(4,place.getZone().toString());
			pStmt.setString(5,place.getReal().toString());
			
			
			conec.setAutoCommit(false);
			rs = pStmt.executeUpdate();
			conec.commit();	
			
			ResultSet keys = pStmt.getGeneratedKeys();
			keys.next();
			rs = keys.getInt(1);
			

			
			}catch(Exception e){
			System.out.println(e.getMessage());
			}
	    
			return rs;
			
		
	}
	
	
	/**
	 * Ins building table.
	 *
	 * @param conec the conec
	 * @param building the building
	 * @param chain the chain
	 * @return the int
	 * @throws Exception the exception
	 */
	public static int insBuildingTable(Connection conec, Building building, String chain) throws Exception {
		int rs=0; 
	    try{
	    	PreparedStatement pStmt = null; 
	    	pStmt = conec.prepareStatement(chain);
			pStmt.setString(1,building.getPlaceName());
			pStmt.setInt(2,building.getCapacity());
			pStmt.setString(3,building.getPlaceDescription());
			pStmt.setString(4,building.getZone().toString());
			pStmt.setString(5,building.getReal().toString());
			
			conec.setAutoCommit(false);
			rs = pStmt.executeUpdate();
			conec.commit();	
			
			ResultSet keys = pStmt.getGeneratedKeys();
			keys.next();
			rs = keys.getInt(1);

			}catch(Exception e){
			System.out.println(e.getMessage());
			}
	    
	    return rs;
		
		
	}
	
	
	//Deletes
	/**
	 * Del place table.
	 *
	 * @param conec the conec
	 * @param idDelete the id delete
	 * @param chain the chain
	 * @return true, if successful
	 * @throws SQLException the SQL exception
	 */
	public static boolean delPlaceTable(Connection conec, int idDelete, String  chain) throws SQLException {
		int rs=0; 
	    try{
	    	PreparedStatement pStmt = null; 
	    	pStmt = conec.prepareStatement(chain);
	    	pStmt.setInt(1,idDelete);

			conec.setAutoCommit(false);
			rs = pStmt.executeUpdate();
			conec.commit();	
			

			
			}catch(Exception e){
			System.out.println(e.getMessage());
			}
	    
			if (rs==1) return true;
			else return false;
		
	}	
	
	
	/**
	 * Del building table.
	 *
	 * @param conec the conec
	 * @param idBuilding the id building
	 * @param chain the chain
	 * @return true, if successful
	 * @throws SQLException the SQL exception
	 */
	public static boolean delBuildingTable(Connection conec, int idBuilding, String  chain) throws SQLException {
		int rs=0; 
	    try{
	    	PreparedStatement pStmt = null; 
	    	pStmt = conec.prepareStatement(chain);
	    	
	    	pStmt.setInt(1,idBuilding);

			conec.setAutoCommit(false);
			rs = pStmt.executeUpdate();
			conec.commit();	
			
			}catch(Exception e){
			System.out.println(e.getMessage());
			}
	    
			if (rs==1) return true;
			else return false;
		
	}	
	
	
	
	//Updates
	
	
	/**
	 * Upd place table.
	 *
	 * @param conec the conec
	 * @param update the update
	 * @param chain the chain
	 * @return true, if successful
	 * @throws SQLException the SQL exception
	 */
	public static boolean updPlaceTable(Connection conec,  Place update, String chain) throws SQLException {
		int rs=0; 
	    try{
	    	PreparedStatement pStmt = null; 

			
	    		pStmt = conec.prepareStatement(chain);
				pStmt.setInt(1,update.getiD());
				pStmt.setInt(2,update.getiD());

				conec.setAutoCommit(false);
				rs = rs + pStmt.executeUpdate();
				conec.commit();	
			
			
			}catch(Exception e){
			System.out.println(e.getMessage());
			}
	    
			if (rs==1) return true;
			else return false;
		
	}
	
		
		/**
		 * Upd building table.
		 *
		 * @param conec the conec
		 * @param building the building
		 * @param chain the chain
		 * @return true, if successful
		 * @throws SQLException the SQL exception
		 */
		public static boolean updBuildingTable(Connection conec, Building building, String chain) throws SQLException {
			int rs=0; 
		    try{
		    	PreparedStatement pStmt = null; 

				
		    		pStmt = conec.prepareStatement(chain);
					pStmt.setInt(1,building.getiD());
					pStmt.setInt(2,building.getiD());

					conec.setAutoCommit(false);
					rs = rs + pStmt.executeUpdate();
					conec.commit();	
				
				
				}catch(Exception e){
				System.out.println(e.getMessage());
				}
		    
				if (rs==1) return true;
				else return false;
			
		}


		/**
		 * Ins id child.
		 *
		 * @param conec the conec
		 * @param key the key
		 * @param chain the chain
		 * @return the int
		 */
		public static int insIdChild(Connection conec, int key, String chain) {
			int rs=0; 
		    try{
		    	PreparedStatement pStmt =conec.prepareStatement(chain);
				pStmt.setInt(1,key);
				
				conec.setAutoCommit(false);
				rs = pStmt.executeUpdate();
				conec.commit();	
				
				}catch(Exception e){
				System.out.println(e.getMessage());
				}
		    
				return rs;
		}


		/**
		 * Show allevents.
		 *
		 * @param conec the conec
		 * @param chain the chain
		 * @return true, if successful
		 */
		public static boolean showAllevents(Connection conec, String chain) {
			  boolean show = false;
			  try{
					
					//Creating a PreparedStatement with a query "select * from Event" 
					PreparedStatement pStmt = conec.prepareStatement(chain);
					
					//Executing this query using executeQuery()
					ResultSet rs = pStmt.executeQuery();
					
					show=true;
					
					//Process the resultSet and print all records on console
					System.out.println("ID    Name  Capacity   Description	Zone  ");//Type Place");
				/**	while(rs.next())
					{
						System.out.println(rs.getInt(1)+" - "+ rs.getString(2) + " - " + rs.getInt(3)+" - "+ rs.getString(4)+" - "+ rs.getString(5));//+" - "+rs.getString(6));
						
					}*/
					//Closing the connection
					conec.close();
					
				}catch (Exception e){
					System.out.println(e.getMessage());
				}
			  
			  return show;
		  }
		
		
		/**
		 * Obtain all places.
		 *
		 * @param conec the conec
		 * @param chain the chain
		 * @return the array list
		 */
		public static ArrayList<Integer> obtainAllPlaces(Connection conec,
				String chain) {
			ArrayList<Integer> pla = new ArrayList<Integer>();

			try{
				
				//Creating a PreparedStatement with a query "select * from Event" 
				PreparedStatement pStmt = conec.prepareStatement(chain);
				
				//Executing this query using executeQuery()
				ResultSet rs = pStmt.executeQuery();
				
				
				while(rs.next())
				{
					pla.add(rs.getInt("PlaceId"));
				}
				//Closing the connection
				conec.close();
				
			}catch (Exception e){
				System.out.println(e.getMessage());
			}
			
			return pla;
		}
		
		/**
		 * Obtain places name id.
		 *
		 * @param con the con
		 * @param chain the chain
		 * @return the hash map
		 */
		public static HashMap<Integer, String> obtainPlacesNameId(
				Connection con, String chain) {
			HashMap<Integer, String> pla = new HashMap<Integer, String>();

			try{
				
				//Creating a PreparedStatement with a query "select * from Event" 
				PreparedStatement pStmt = con.prepareStatement(chain);
				
				//Executing this query using executeQuery()
				ResultSet rs = pStmt.executeQuery();
				
				
				while(rs.next())
				{
					pla.put(rs.getInt("PlaceId"), rs.getString("Name"));
				}
				//Closing the connection
				con.close();
				
			}catch (Exception e){
				System.out.println(e.getMessage());
			}
			
			return pla;
		}

		
		/**
		 * Show places id.
		 *
		 * @param conec the conec
		 * @param chain the chain
		 * @param id the id
		 * @return true, if successful
		 */
		public static boolean showPlacesId(Connection conec, String chain, int id) {
			  boolean show = false;
			  try{
					
					//Creating a PreparedStatement with a query "select * from Event" 
					PreparedStatement pStmt = conec.prepareStatement(chain);
					pStmt.setInt(1,id);
				
					//Executing this query using executeQuery()
					ResultSet rs = pStmt.executeQuery();
					
					show=true;
					
					//Process the resultSet and print all records on console
				/**	System.out.println("ID    Name  Capacity   Description	Zone  ");//Type Place");
					while(rs.next())
					{
						System.out.println(rs.getInt(1)+" - "+ rs.getString(2) + " - " + rs.getInt(3)+" - "+ rs.getString(4)+" - "+ rs.getString(5));//+" - "+rs.getString(6));
						
					}*/
					//Closing the connection
					conec.close();
					
				}catch (Exception e){
					System.out.println(e.getMessage());
				}
			  
			  return show;
		  }

}
